'''
-- 微信小程序用户和设备绑定表
CREATE TABLE IF NOT EXISTS wx_user_device_bind (
    id SERIAL PRIMARY KEY, -- 绑定ID，唯一标识每个绑定关系
    user_id INT NOT NULL, -- 用户ID
    device_id VARCHAR(50) NOT NULL, -- 设备ID
    create_time TIMESTAMP DEFAULT now(), -- 绑定时间
    status VARCHAR(50) DEFAULT 'bind', -- 绑定状态1：bind，2：unbind
    -- 添加外键约束，关联 users 表
    FOREIGN KEY (user_id) REFERENCES wx_user(id);
    -- 添加外键约束，关联 devices 表
    FOREIGN KEY (device_id) REFERENCES devices(id)
);
SELECT SETVAL('wx_user_device_bind_id_seq',max(id),TRUE) FROM wx_user_device_bind;

'''

import uuid
import datetime
import logging
import psycopg2


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
class WxUserDeviceBind: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    # 插入绑定关系
    def insert_bind(self, user_id, device_id):
        sql = "INSERT INTO wx_user_device_bind (user_id, device_id) VALUES (%s, %s)"
        values = (user_id, device_id)
        try:
            self.cursor.execute(sql, values)

        except Exception as e:
            logger.error(f"insert bind error: {e}")
            return False, str(e)
        logger.info(f"insert bind success user_id: {user_id} device_id: {device_id}")

        return True, "insert bind success"
    
    # 查询绑定关系：根据设备ID，和用户ID查询绑定关系
    def query_bind_by_device_id_user_id(self, device_id, user_id):
        sql = "SELECT * FROM wx_user_device_bind WHERE device_id = %s AND user_id = %s"
        values = (device_id, user_id)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchone()

        return result
        

    # 查询绑定关系
    def query_bind_by_user_id(self, user_id, bind_status='bind'):
        sql = "SELECT device_id FROM wx_user_device_bind WHERE user_id = %s AND status = %s"
        values = (user_id, bind_status)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchall()

        return result

    # 升级绑定关系
    def update_bind_status(self, user_id, device_id, bind_status):
        sql = "UPDATE wx_user_device_bind SET status = %s WHERE user_id = %s AND device_id = %s"
        values = (bind_status, user_id, device_id)
        self.cursor.execute(sql, values)
        logger.info(f"update bind status success user_id: {user_id} device_id: {device_id} status: {bind_status}")

        return True
    
    # 删除绑定关系
    def delete_bind(self, user_id, device_id):
        sql = "DELETE FROM wx_user_device_bind WHERE user_id = %s AND device_id = %s"
        values = (user_id, device_id)
        self.cursor.execute(sql, values)
        logger.info(f"delete bind success user_id: {user_id} device_id: {device_id}")

        return True